-- This file assumes that you have run 01_ownershipchain.sql.
SET NOCOUNT ON
USE PermTest
go
-- 1. Create a certificate. Must specify password and subject.
CREATE CERTIFICATE DemoCert
ENCRYPTION BY PASSWORD = N'Jolka, Jolka pamiętasz'
WITH SUBJECT = N'A certificate to demonstrate procedure signing'
go

-- 2. Sign the procedure with the certificate.
ADD SIGNATURE TO TestSP BY CERTIFICATE DemoCert
    WITH PASSWORD = N'Jolka, Jolka pamiętasz'
go
-- This is how we can view that procedure is signed:
SELECT s.name + '.' + o.name AS object, c.name AS [Certificate], cp.*
FROM   sys.crypt_properties cp
JOIN   sys.certificates c ON c.thumbprint = cp.thumbprint
JOIN   sys.objects o ON cp.major_id = o.object_id
JOIN   sys.schemas s ON s.schema_id = o.schema_id
go

-- 3. Create a user from the certificate. The user is needed to connect the
-- certificate to the permission.
CREATE USER DemoCertUser FROM CERTIFICATE DemoCert
go
-- See this user in sys.database_principals
SELECT name, principal_id, type, type_desc,
       authentication_type_desc
FROM   sys.database_principals
go

-- 4. Grant the user the permission needed.
GRANT SELECT ON PermTable TO DemoCertUser
go

-- Now we can test with our testuser. This is how the procedure looks like at
-- this point:
/*
ALTER PROCEDURE TestSP @id int, @title nvarchar(40) AS
   SELECT * FROM tokeninfo
   IF object_id('PermTable') IS NOT NULL
   BEGIN
      INSERT PermTable (id, title) VALUES (@id, @title)
      SELECT id, title, enteredby FROM PermTable
   END
   ELSE
      PRINT 'No table to day'
*/

EXECUTE AS USER = 'TestUser'
go
EXEC TestSP 7, N'Wino, śpiew i łzy'
go
REVERT
go

-- What happens if we change the procedure? We modify the message.
ALTER PROCEDURE TestSP @id int, @title nvarchar(40) AS
   SELECT * FROM tokeninfo
   IF object_id('PermTable') IS NOT NULL
   BEGIN
      INSERT PermTable (id, title) VALUES (@id, @title)
      SELECT id, title, enteredby FROM PermTable
   END
   ELSE
      PRINT 'PermTable has not been created yet.'
go

EXECUTE AS USER = 'TestUser'
go
EXEC TestSP 8, N'Ostatnie ogłoszenie'
-- Table is said to be missing. Note that the certificate is no
-- longer in the user tokens.
go
REVERT
go

-- Signature was lost since contents changed, procedure must be re-signed.
SELECT s.name + '.' + o.name AS object, c.name AS [Certificate], cp.*
FROM   sys.crypt_properties cp
JOIN   sys.certificates c ON c.thumbprint = cp.thumbprint
JOIN   sys.objects o ON cp.major_id = o.object_id
JOIN   sys.schemas s ON s.schema_id = o.schema_id
go


ADD SIGNATURE TO TestSP BY CERTIFICATE DemoCert
    WITH PASSWORD = N'Jolka, Jolka pamiętasz'
go

-- Try again.
EXECUTE AS USER = 'TestUser'
go
EXEC TestSP 8, N'Ostatnie ogłoszenie'
go
REVERT
go

-- Let's now see if we can overcome other limitations with ownership
-- chaining. For instance, dynamic SQL?
ALTER PROCEDURE TestSP @id int, @title nvarchar(40) AS
   SELECT * FROM tokeninfo
   INSERT PermTable (id, title) VALUES (@id, @title)
   SELECT id, title, enteredby FROM PermTable
   PRINT 'The dynamic part'
   EXEC('SELECT id, title, enteredby
         FROM   PermTable
         WHERE  title = N''' + @title + '''')
go
ADD SIGNATURE TO TestSP BY CERTIFICATE DemoCert
    WITH PASSWORD = N'Jolka, Jolka pamiętasz'
go

-- Test it!
EXECUTE AS USER = 'TestUser'
go
EXEC TestSP 9, N'Memu miastu na do widzenia'
go
REVERT
go

-- Our test user has been a naughty boy why will we explicitly deny
-- him access to the PermTable.
DENY SELECT, INSERT, UPDATE, DELETE ON PermTable TO TestUser
go
-- Now we trying running the first test procedure again
EXECUTE AS USER = 'TestUser'
go
EXEC TestSP 10, N'Martwe morze'
-- The Dynamic SQL fails, but the INSERT still works - ownership chaining.
go
REVERT
go

-- Remove the DENY - it is best avoided.
REVOKE SELECT, INSERT, UPDATE, DELETE ON PermTable TO TestUser
go


-- The certificate propagates into dynamic SQL. What about other scopes?
-- We create a trigger.
CREATE TRIGGER PermTable_tri ON PermTable FOR INSERT AS
  SELECT 'PermTable_tri', * FROM tokeninfo
go
-- And an inner procedure.
CREATE PROCEDURE inner_sp AS
   SELECT 'inner_sp', * FROM tokeninfo
go

-- Change the test procedure to call these and a system procedure
ALTER PROCEDURE TestSP @id int, @title nvarchar(40) AS
   SELECT * FROM tokeninfo
   EXEC sp_helpindex PermTable
   EXEC inner_sp
   INSERT PermTable (id, title) VALUES (@id, @title)
   SELECT id, title, enteredby FROM PermTable
go

-- Try before signing.
EXECUTE AS USER = 'TestUser'
go
EXEC TestSP 11, N'Planeta smoka'
-- sp_helpindex errors out.
go
REVERT
go

-- Sign it.
ADD SIGNATURE TO TestSP BY CERTIFICATE DemoCert
    WITH PASSWORD = 'Jolka, Jolka pamiętasz'
go

EXECUTE AS USER = 'TestUser'
go
EXEC TestSP 12, N'Giganci tańczą'
-- sp_helpindex works, but note that cert token is not in inner_sp
-- and the trigger!
go
REVERT
go

-- Drop trigger for now.
DROP TRIGGER PermTable_tri
